package com.balloon.io;

import com.balloon.logic.City;
import com.balloon.logic.Property;
import com.balloon.logic.Sightspot;
import java.sql.*;

/**
 * This class is used for handling data between city and the database
 */
public class CityDao {

    static Connection currentCon = null;
    static ResultSet rs = null;
    static Statement stmt = null;

    /**
     * This method is used for getting city information from the database
     * @param city
     * @return
     */
    public static City getCityInformation(City city) {
        String cityName = city.getName();
        System.out.println("Selected city is " + cityName);
        int cityID = 0;
        try {
            //connect to DB
            currentCon = ConnectionManager.getConnection();
            stmt = currentCon.createStatement();
            //Query for cityID and longitude and latitude
            String selectQuery =
                    "select * from city where cityName='"
                    + cityName
                    + "'";
            rs = stmt.executeQuery(selectQuery);
            System.out.println(selectQuery);
            boolean more = rs.next();
            if (!more) {
                System.out.println("Error happened");
            } else if (more) {
                cityID = Integer.parseInt(rs.getString("cityID"));
                double locationX = Double.parseDouble(rs.getString("longitude"));
                double locationY = Double.parseDouble(rs.getString("latitude"));

                city.setLocationX(locationX);
                city.setLocationY(locationY);
                city.setcityID(cityID);
                System.out.println(cityID);
            }

            /*selectQuery =
            "select * from citycomment where cityID='"
            + cityID
            + "'";

            rs = stmt.executeQuery(selectQuery);
            while(rs.next()){
            String commentDescription=rs.getString("description");
            String personEmail=rs.getString("person");
            String postTime=rs.getString("postTime");

            city.addComments(personEmail+"says: "+commentDescription +"at " +postTime);
            }*/

            //Query for city rank
           /* selectQuery =
            "select * from cityrank where cityID='"
            + cityID
            + "'";

            int rank = 0;
            int tempCounter = 0;
            rs = stmt.executeQuery(selectQuery);
            while (rs.next()) {
            rank += Integer.parseInt(rs.getString("rank"));
            tempCounter++;
            }

            city.setRank(rank / tempCounter);

            /*selectQuery =
            "select * from citylink where cityID='"
            + cityID
            + "'";

            rs = stmt.executeQuery(selectQuery);
            while(rs.next()){
            String website=rs.getString("website");
            String description=rs.getString("description");
            }*/

            //Query for city information
           /* selectQuery =
            "select * from sightSpot LEFT JOIN address ON " +
            "sightSpot.addressID=address.addressID where cityID='"
            + cityID
            + "'";
             */
            selectQuery =
                    "select S.name,S.description,D.longitude,D.latitude"
                    + " from ecom1.sightSpot S, ecom1.address D, ecom1.city C"
                    + " where S.addressID=D.addressID and D.cityID='"
                    + cityID
                    + "'";

            rs = stmt.executeQuery(selectQuery);


            String sightName = "";
            double sightLongitude = 0;
            double sightLatitude = 0;
            String sightDescription = "";
            String addressID = "";
            while (rs.next()) {
                sightName = rs.getString("name");
                sightDescription = rs.getString("description");
                sightLongitude = Double.parseDouble(rs.getString("longitude"));
                sightLatitude = Double.parseDouble(rs.getString("latitude"));

                Sightspot sight = new Sightspot(sightName, sightLongitude, sightLatitude, sightDescription);
                city.addSightspot(sight);

            }

            //find properties(vendor)
            selectQuery =
                    "select S.propertyName,S.description,D.longitude,D.latitude"
                    + " from ecom1.properties S, ecom1.address D, ecom1.city C"
                    + " where S.addressID=D.addressID and D.cityID='"
                    + cityID
                    + "'";

            rs = stmt.executeQuery(selectQuery);

            String propertyName = "";
            double propertyLongitude = 0;
            double propertyLatitude = 0;
            String propertyDescription = "";
            while (rs.next()) {
                propertyName = rs.getString("propertyName");
                propertyLongitude = Double.parseDouble(rs.getString("longitude"));
                propertyLatitude = Double.parseDouble(rs.getString("latitude"));
                propertyDescription = rs.getString("description");

                Property property = new Property(propertyName, propertyLongitude, propertyLatitude, propertyDescription);
                city.addProperty(property);


            }

        } catch (Exception ex) {
            System.out.println("Log In failed: An Exception has occurred! " + ex);
        } //Some exception handling
        finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e) {
                }
                rs = null;
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception e) {
                }
                stmt = null;
            }

            if (currentCon != null) {

                try {
                    currentCon.close();
                } catch (Exception e) {
                }
                currentCon = null;

            }
        }

        return city;
    }
}


